--  01 -> hdfs数据  ods_nshop.ods_01_action_log 行为日志表
--  02 -> mysql数据 ods_nshop.ods_02_user 用户表

--1、 创建数据库
create database if not exists ods_nshop;
use ods_nshop;

-- 2、ods层相关表创建
-- 2.1 业务数据导入
-- =============用户相关表=============
-- 用户基本信息表
drop table ods_nshop.ods_02_customer;
create table if not exists ods_nshop.ods_02_customer
(
    customer_id         string COMMENT '用户ID',
    customer_login      string COMMENT '用户登录名',
    customer_nickname   string COMMENT '用户名(昵称)',
    customer_name       string COMMENT '用户真实姓名',
    customer_pass       string COMMENT '用户密码',
    customer_mobile     string COMMENT '用户手机',
    customer_idcard     string COMMENT '身份证',
    customer_gender     TINYINT COMMENT '性别：1男 0女',
    customer_birthday   string COMMENT '出生年月',
    customer_age        TINYINT COMMENT '年龄',
    customer_age_range  string COMMENT '年龄段',
    customer_email      string COMMENT '用户邮箱',
    customer_natives    string COMMENT '所在地区',
    customer_ctime      bigint COMMENT '创建时间',
    customer_utime      bigint COMMENT '修改时间',
    customer_device_num string COMMENT '用户手机设备号'
)
location '/data/nshop/ods/ods_02_customer/';

-- 用户关注表
create table if not exists ods_nshop.ods_02_customer_attention
(
    customer_id      string COMMENT '用户ID',
    attention_id     string COMMENT '关注对象ID',
    attention_type   TINYINT COMMENT '关注类型：3店铺 4商品',
    attention_status bigint COMMENT '关注状态：1关注 0取消',
    attention_ctime  bigint COMMENT '生成时间'
)
location '/data/nshop/ods/ods_02_customer_attention/';

-- 用户收货地址管理表
create table if not exists ods_nshop.ods_02_customer_consignee
(
    consignee_id      string COMMENT '收货地址ID',
    customer_id       string COMMENT '用户ID',
    consignee_name    string COMMENT '收货人',
    consignee_mobile  string COMMENT '收货人电话',
    consignee_zipcode string COMMENT '收货人地区',
    consignee_addr    string COMMENT '收货人详细地址',
    consignee_tag     string COMMENT '标签：1家 2公司 3学校',
    ctime             bigint COMMENT '创建时间'
)
location '/data/nshop/ods/ods_02_customer_consignee/';

-- =============订单相关表=============
-- 订单表
drop table ods_nshop.ods_02_orders;
create table if not exists ods_nshop.ods_02_orders
(
    order_id          string COMMENT '订单ID(时间+商品ID+4位随机)',
    customer_id       string COMMENT '下单用户ID',
    order_status      TINYINT COMMENT '订单状态',
    customer_ip       string COMMENT '下单用户IP',
    user_longitude    string COMMENT '用户地理：经度',
    user_latitude     string COMMENT '用户地理：纬度',
    user_areacode     string COMMENT '用户所在地区',
    consignee_name    string COMMENT '收货人',
    consignee_mobile  string COMMENT '收货人电话',
    consignee_zipcode string COMMENT '收货人地址',
    pay_type          string COMMENT '支付类型：线上支付 10 网上银行 11 微信 12 支付宝 | 线下支付(货到付款) 20 ',
    pay_code          string COMMENT '支付对应唯一标识，如微信号、支付宝号',
    pay_nettype       string COMMENT '支付网络方式：0 wifi | 1 4g | 2 3g |3 线下支付',
    district_money    DECIMAL(8, 1) COMMENT '优惠金额',
    shipping_money    DECIMAL(8, 1) COMMENT '运费金额',
    payment_money     DECIMAL(10, 1) COMMENT '支付金额',
    order_ctime       bigint COMMENT '创建时间',
    shipping_time     bigint COMMENT '发货时间',
    receive_time      bigint COMMENT '收货时间'
)
location '/data/nshop/ods/ods_02_orders/';
-- 订单详情表
create table if not exists ods_nshop.ods_02_order_detail
(
    order_detail_id    string COMMENT '订单详情表ID',
    order_id           string COMMENT '订单表ID',
    product_id         string COMMENT '订单商品ID',
    product_name       string COMMENT '商品名称',
    product_remark     string COMMENT '商品描述',
    product_cnt        INT COMMENT '购买商品数量',
    product_price      DECIMAL(5, 1) COMMENT '购买商品单价',
    weighing_cost      DECIMAL(2, 1) COMMENT '商品加权价格',
    district_money     DECIMAL(4, 1) COMMENT '优惠金额',
    is_activity        bigint COMMENT '1:参加活动|0：没有参加活动',
    order_detail_ctime bigint COMMENT '下单时间'
)
location '/data/nshop/ods/ods_02_order_detail/';
-- 订单支付记录表
drop table ods_nshop.ods_02_orders_pay_records;
create table if not exists ods_nshop.ods_02_orders_pay_records
(
    pay_id      string COMMENT '支付记录ID',
    order_id    string COMMENT '订单ID',
    customer_id string COMMENT '用户ID',
    pay_status  string COMMENT '支付状态：0 支付失败| 1 支付成功',
    pay_type    string COMMENT '支付类型：线上支付 10 网上银行 11 微信 12 支付宝 | 线下支付(货到付款) 20 ',
    pay_code    string COMMENT '支付对应唯一标识，如微信号、支付宝号',
    pay_nettype string COMMENT '支付网络方式：1 wifi | 2 4g | 3 3g |4 线下支付',
    pay_amount  double COMMENT '支付金额',
    pay_ctime   bigint COMMENT '创建时间'
)
location '/data/nshop/ods/ods_02_orders_pay_records/';
-- =============商品相关表=============
-- 商品分类表
create table if not exists ods_nshop.dim_pub_category
(
    category_code      string COMMENT '分类编码',
    category_name      string COMMENT '分类名称',
    category_parent_id string COMMENT '父分类ID',
    category_status    TINYINT COMMENT '分类状态：0禁止，1启用',
    category_utime     bigint COMMENT '最后修改时间'
)
location '/data/nshop/ods/dim_pub_category/';

-- 商品信息表
create table if not exists ods_nshop.dim_pub_product
(
    product_code           string COMMENT '商品ID(分类编码+供应商编码+编号)',
    product_name           string COMMENT '商品名称',
    product_remark         string COMMENT '商品描述',
    category_code          string COMMENT '分类ID',
    supplier_code          string COMMENT '商品的供应商编码',
    product_price          DECIMAL COMMENT '商品销售价格',
    product_weighing_cost  DECIMAL(2, 1) COMMENT '商品加权价格',
    product_publish_status TINYINT COMMENT '上下架状态：0下架 1上架',
    product_audit_status   TINYINT COMMENT '审核状态：0未审核，1已审核',
    product_bar_code       string COMMENT '国条码',
    product_weight         FLOAT COMMENT '商品重量',
    product_length         FLOAT COMMENT '商品长度',
    product_height         FLOAT COMMENT '商品高度',
    product_width          FLOAT COMMENT '商品宽度',
    product_colors         SMALLINT COMMENT '0:白|1:赤|2:红|3:黄|4:绿|5:青|6:蓝|7:紫|8:黑|9:彩色',
    product_date           string COMMENT '生产日期',
    product_shelf_life     bigint COMMENT '商品有效期',
    product_ctime          bigint COMMENT '商品录入时间',
    product_utime          bigint COMMENT '最后修改时间'
)
location '/data/nshop/ods/dim_pub_product/';

-- 供应商信息表
create table if not exists ods_nshop.dim_pub_supplier
(
    supplier_code   string COMMENT '供应商编码',
    supplier_name   string COMMENT '供应商名称',
    supplier_type   TINYINT COMMENT '供应商类型：1.自营，2.官方 3其他',
    supplier_status TINYINT COMMENT '状态：0禁止，1启用',
    supplier_utime  bigint COMMENT '最后修改时间'
)
location '/data/nshop/ods/dim_pub_supplier/';
-- 页面布局表
create table if not exists ods_nshop.dim_pub_page
(
    page_code   string COMMENT '页面编码',
    page_remark string COMMENT '页面描述',
    page_type   string COMMENT '页面类型(1:导航页 2：分类页 3：店铺页 4：产品页)',
    page_target string COMMENT '页面对应实体编号(如产品、店铺)',
    page_ctime  bigint COMMENT '创建时间'
)
location '/data/nshop/ods/dim_pub_page/';
-- =============公共参数表=============
-- 地域字典表
create table if not exists ods_nshop.dim_pub_area
(
    region_code          string COMMENT '地区编码 如110105  | 130406 ',
    region_code_desc     string COMMENT '地区编码 如朝阳区 | 峰峰矿区',
    region_city          string COMMENT '地区编码 如1101 北京市朝阳区 | 1304 邯郸',
    region_city_desc     string COMMENT '地区编码 如1101 | 1304 邯郸市',
    region_province      string COMMENT '地区编码 如11 北京市 | 13 河北省',
    region_province_desc string COMMENT '地区编码 如 北京市 | 河北'
)
location '/data/nshop/ods/dim_pub_area/';
-- 通用字典表--暂无 ods_nshop.dim_pub_comm

-- 时间字典表
create table if not exists ods_nshop.dim_pub_date
(
    date_day          string COMMENT '日期：yyyyMMdd 如20190520',
    date_day_desc     string COMMENT '日期格式：yyyy年MM月dd日 如2019年05月20日',
    date_day_month    string COMMENT '日期：20 本月第几天 如2019年5月20日为5月第20天',
    date_day_year     string COMMENT '日期：139 本年第几天 如2019年5月20日为139天',
    date_day_en       string COMMENT '日期：monday 星期几',
    date_week         string COMMENT '周：本月第几周 4 如 20190520为本月第4周',
    date_week_desc    string COMMENT '周：本月第几周 如 20190504',
    date_month        string COMMENT '月: 5 如 20190520为本年5月',
    date_month_en     string COMMENT '月: may ',
    date_month_desc   string COMMENT '月：如 201905',
    date_quarter      string COMMENT '季度:2',
    date_quarter_en   string COMMENT '季度:Q2',
    date_quarter_desc string COMMENT '季度:2019Q2 如 20190520为2019Q2',
    date_year         string COMMENT '年:2019'
)
location '/data/nshop/ods/dim_pub_date/';

-- 2.2 埋点日志数据导入
-- 用户行为日志表
drop table ods_nshop_01_useractlog;
create external table if not exists ods_nshop.ods_nshop_01_useractlog
(
    action       string comment '行为类型:install安装|launch启动|interactive交互|page_enter_h5页面曝光|page_enter_native页面进入|exit退出',
    event_type   string comment '行为类型:click点击|view浏览|slide滑动|input输入',
    customer_id  string comment '用户id',
    device_num   string comment '设备号',
    device_type  string comment '设备类型',
    os           string comment '手机系统',
    os_version   string comment '手机系统版本',
    manufacturer string comment '手机制造商',
    carrier      string comment '电信运营商',
    network_type string comment '网络类型',
    area_code    string comment '地区编码',
    longitude    string comment '经度',
    latitude     string comment '纬度',
    extinfo      string comment '扩展信息(json格式)',
    duration     string comment '停留时长',
    ct           bigint comment '创建时间'
) partitioned by (bdp_day string)
    ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
    STORED AS TEXTFILE
    location '/data/nshop/ods/user_action_log/';

drop table ods_nshop_01_useractlog2;
create external table if not exists ods_nshop.ods_nshop_01_useractlog2
(
    action       string comment '行为类型:install安装|launch启动|interactive交互|page_enter_h5页面曝光|page_enter_native页面进入|exit退出',
    event_type   string comment '行为类型:click点击|view浏览|slide滑动|input输入',
    customer_id  string comment '用户id',
    device_num   string comment '设备号',
    device_type  string comment '设备类型',
    os           string comment '手机系统',
    os_version   string comment '手机系统版本',
    manufacturer string comment '手机制造商',
    carrier      string comment '电信运营商',
    network_type string comment '网络类型',
    area_code    string comment '地区编码',
    longitude    string comment '经度',
    latitude     string comment '纬度',
    extinfo      string comment '扩展信息(json格式)',
    duration     string comment '停留时长',
    ct           bigint comment '创建时间'
) partitioned by (bdp_day string)
    ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
    STORED AS TEXTFILE
    location '/data/nshop/ods/user_action_log/';

desc ods_nshop.ods_nshop_01_useractlog;

-- 2.3 外部数据导入
-- 广告投放信息
create external table if not exists ods_nshop.ods_nshop_01_releasedatas
(
    device_num      string comment '设备号',
    device_type     string comment '设备类型',
    os              string comment '手机系统',
    os_version      string comment '手机系统版本',
    manufacturer    string comment '手机制造商',
    area_code       string comment '地区编码',
    release_sid     string comment '投放请求id',
    release_session string comment '投放会话id',
    release_sources string comment '投放渠道',
    release_params  string comment '投放请求参数',
    ct              bigint comment '创建时间'
) partitioned by (bdp_day string)
    ROW FORMAT DELIMITED
        FIELDS TERMINATED BY ','
    stored as textfile
    location '/data/nshop/ods/release/';
